﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ECCIT;
using ECCIT.Data;
using ECCIT.Web;
using OgilvyOne.Model;
using OgilvyOne.IDAL;
namespace OgilvyOne.DAL
{
    public class UserDAL:IDALUser
    {
       /// <summary>
       /// 保存xml
       /// </summary>
       /// <param name="sb"></param>
       /// <param name="guid"></param>
       /// <returns></returns>
        public int SaveXml(StringBuilder sb,string guid,string lang)
        {
           try
           {
               string strSql = string.Format("insert into blogXml (guid,strXml,lang) values ('{0}','{1}','{2}')", guid, sb, lang);
               int obj = DBHelper.ExecuteNonQuery(strSql);
               return obj;
           }
           catch (Exception)
           {
               throw;
           }
            
        }
        /// <summary>
        /// 获取xml
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public string GetXml(string guid)
        {
            string strSql = string.Format("select strXml from blogXml where guid='{0}'", guid);
            string result="";
            using (IDataReader reader = DBHelper.ExecuteReader(strSql)) 
            {
                while (reader.Read())
                {
                    result = (string)reader["strXml"];
                }
            }
            return result;
        }

        /// <summary>
        /// 删除xml
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public int DeleXml(string guid)
        {
            string strSql = string.Format("delete blogXml where guid='{0}'", guid);
            int obj = DBHelper.ExecuteNonQuery(strSql);
            return obj;
        }
        /// <summary>
        /// 保存注册邮箱
        /// </summary>
        /// <param name="surName"></param>
        /// <param name="presonalName"></param>
        /// <param name="toMail1"></param>
        /// <param name="toMail2"></param>
        /// <param name="toMail3"></param>
        /// <param name="lang"></param>
        /// <returns></returns>
        public int SaveSendMail(string surName, string presonalName, string toMail1, string toMail2, string toMail3, string lang, string friendName1, string friendName2, string friendName3,string userEmail,string mobile)
        {
            string strSql = string.Format("insert into sendEmail (surName,presonalName,userEmail,mobile,friendEmail1,friendEmail2,friendEmail3,language,friendName1,friendName2,friendName3) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", surName, presonalName, userEmail, mobile, toMail1, toMail2, toMail3, lang, friendName1, friendName2, friendName3);
            return DBHelper.ExecuteNonQuery(strSql);
        }

        public int IsExist(string email)
        {
            string strSql = string.Format("select count(userEmail) from sendEmail where userEmail='{0}'", email);
            object obj=DBHelper.ExecuteScalar(strSql);

            return (int)obj;
        }

        public DataTable GetExcel(string startTime,string endTime)
        {
            string strSql = string.Format("select surName,presonalName,userEmail,mobile,friendName1,friendEmail1,friendName2,friendEmail2,friendName3,friendEmail3,language,creatTime from dbo.sendEmail where creatTime between '{0}' and '{1}' order by language", startTime, endTime);
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }

        public int SaveContacUs(string title, string name, string email, string country,string message, string lang)
        {
            string strSql = string.Format("insert into contactUs (title,name,email,country,messages,lang) values ('{0}','{1}','{2}','{3}','{4}','{5}')", title, name, email, country, message,lang);
            return DBHelper.ExecuteNonQuery(strSql);
        }

        public int CheckEmailOFContacUs(string email)
        {
            string strSql = string.Format("select count(email) from contactUs where email='{0}'", email);
            object obj = DBHelper.ExecuteScalar(strSql);
            return (int)obj;
        }
    }
}
